package com.enation.app.javashop.core.statistics.service.impl;

import com.enation.app.javashop.core.shop.model.vo.ShopInfoVO;
import com.enation.app.javashop.core.shop.service.ShopManager;
import com.enation.app.javashop.core.statistics.service.AppStatisticManager;
import com.enation.app.javashop.framework.context.UserContext;
import com.enation.app.javashop.framework.database.DaoSupport;
import com.enation.app.javashop.framework.util.CurrencyUtil;
import com.enation.app.javashop.framework.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

@Service
public class AppStatisticManagerImpl implements AppStatisticManager {
    @Autowired
    @Qualifier("sssDaoSupport")
    private DaoSupport daoSupport;

    @Autowired
    private ShopManager shopManager;

    /**
     * 是否是社区团购店，获取店铺所在城市
     */
    @Override
    public ShopInfoVO getShopInfo() {
        return shopManager.getShopInfo(UserContext.getSeller().getSellerId());
    }
    /**
     * 支付订单数
     */
    @Override
    public Integer paidOrderNum() {
        String sql = " SELECT COUNT(1) AS 'paid_order_num' FROM es_order " +
                " WHERE pay_status = 'PAY_YES' AND service_status != 'APPLY' AND seller_id = ? " +
                " AND create_time >= ? AND create_time <= ? ";
        return this.daoSupport.queryForInt(sql,UserContext.getSeller().getSellerId(), DateUtil.startOfTodDay(),DateUtil.endOfTodDay());
    }

    /**
     * 交易额
     */
    @Override
    public Double tradeAmount() {
        String sql = " SELECT SUM(goods_price) AS 'trade_amount' FROM es_order " +
                " WHERE pay_status = 'PAY_YES' AND service_status != 'APPLY'  " +
                " AND seller_id = ? AND create_time >= ? AND create_time <= ? ";
        return this.daoSupport.queryForDouble(sql,UserContext.getSeller().getSellerId(), DateUtil.startOfTodDay(),DateUtil.endOfTodDay());
    }

    /**
     * 客单价
     */
    @Override
    public Double orderAvgPrice() {
        String sql = " SELECT COUNT(DISTINCT member_id) AS 'single_user' FROM es_order " +
                " WHERE pay_status = 'PAY_YES' AND service_status != 'APPLY' " +
                " AND seller_id = ? AND create_time >= ? AND create_time <= ? ";
        // 下单用户
        Integer singleUser = this.daoSupport.queryForInt(sql, UserContext.getSeller().getSellerId(), DateUtil.startOfTodDay(),DateUtil.endOfTodDay());
        // 订单交易额
        Double tradeAmount = tradeAmount();
        Double orderAvgPrice = 0.0;
        if(singleUser != 0){
            orderAvgPrice  = Double.valueOf(String.format("%.1f", CurrencyUtil.div(tradeAmount, singleUser)));
        }
        return orderAvgPrice;
    }

    /**
     * 团长总数
     */
    @Override
    public Integer distributorNum() {
        String sql = " SELECT count(1) AS 'distributor_num' FROM es_distribution " +
                " WHERE audit_status = 2 AND `status` = 1  AND city = ? AND audit_time <= ? ";
        String shopCity = getShopInfo().getShopCity();
        return this.daoSupport.queryForInt(sql,shopCity,DateUtil.endOfTodDay());
    }

    /**
     * 团均订单数
     */
    @Override
    public Double distributorOrderNum() {
        Double distributorOrderNum = 0.0;
        // 支付订单数
        Integer paidOrderNum = paidOrderNum();
        Integer distributorNum = distributorNum();
        if(distributorNum != 0){
            distributorOrderNum = Double.valueOf(String.format("%.1f",CurrencyUtil.div(paidOrderNum,distributorNum)));
        }
        return distributorOrderNum;
    }

    /**
     * 团长出单率
     */
    @Override
    public Double distributorOrderRatio() {
        String sql = " SELECT count(DISTINCT d.member_id) AS 'distributor_order_ratio' FROM es_distribution d " +
                " LEFT JOIN es_distribution_order dis ON d.member_id = dis.member_id_lv1 " +
                " LEFT JOIN es_order o ON o.order_id = dis.order_id WHERE d.audit_status = 2 AND d.`status` = 1 " +
                " AND o.pay_status = 'PAY_YES' AND o.service_status != 'APPLY' " +
                " AND d.city = ?  AND dis.create_time >= ? AND dis.create_time <= ? ";
        String shopCity = getShopInfo().getShopCity();
        // 有订单的团长数
        Double haveDistributorOrderNum = this.daoSupport.queryForDouble(sql, shopCity, DateUtil.startOfTodDay(), DateUtil.endOfTodDay());
        // 团长总数
        Integer distributorNum = distributorNum();
        Double distributorOrderRatio = 0.0;
        if(distributorNum != 0){
            distributorOrderRatio = CurrencyUtil.mul(Double.valueOf(String.format("%.3f",CurrencyUtil.div(haveDistributorOrderNum, distributorNum))), 100);
        }
        return distributorOrderRatio ;
    }

    /**
     * 自提点总数
     */
    @Override
    public Integer siteNum() {
        String sql = " SELECT count(1) AS 'site_num'  FROM es_leader WHERE audit_status = 2 AND `status` = 1 " +
                " AND city = ? AND audit_time <= ? ";
        String shopCity = getShopInfo().getShopCity();
        return this.daoSupport.queryForInt(sql,shopCity,DateUtil.endOfTodDay());
    }

    /**
     * 点均订单数
     */
    @Override
    public Double siteOrderNum() {
        //支付订单数
        Integer paidOrderNum = paidOrderNum();
        // 自提点总数
        Integer siteNum = siteNum();
        Double siteOrderNum = 0.0;
        if(siteNum != 0 ){
            siteOrderNum =Double.valueOf(String.format("%.1f",CurrencyUtil.div(paidOrderNum, siteNum)));
        }
        return siteOrderNum;
    }

    /**
     * 自提点出单率
     */
    @Override
    public Double siteOrderRatio() {
        String shopCity = getShopInfo().getShopCity();
        String sql = " SELECT count(DISTINCT l.leader_id) AS 'site_order_ratio'  from es_leader l   " +
                " LEFT JOIN es_order o on o.leader_id = l.leader_id where l.audit_status = 2 AND l.`status` = 1 " +
                " AND l.city = ? AND o.create_time >= ? AND o.create_time <= ? ";
        //有订单的自提点 数量
        Integer haveSitOrderNum = this.daoSupport.queryForInt(sql, shopCity, DateUtil.startOfTodDay(), DateUtil.endOfTodDay());
        // 自提点总数
        Integer siteNum = siteNum();
        Double siteOrderRatio = 0.0;
        if(siteNum != 0){
            siteOrderRatio = CurrencyUtil.mul(Double.valueOf(String.format("%.3f", CurrencyUtil.div(haveSitOrderNum, siteNum))),100);
        }
        return siteOrderRatio;
    }

    /**
     * 待发货 总数
     */
    @Override
    public String waitPayOrderNum() {
        String sql = " SELECT COUNT(1) AS 'wait_pay_order_num' FROM es_order WHERE pay_status = 'PAY_YES' " +
                " AND service_status != 'APPLY'  AND seller_id = ? AND create_time >= ? AND create_time <= ? ";
        return this.daoSupport.queryForInt(sql,UserContext.getSeller().getSellerId(),DateUtil.startOfTodDay(),DateUtil.endOfTodDay()).toString();
    }

    /**
     * 待退款 总数
     */
    @Override
    public String afterSaleOrderNum() {
        String sql = " SELECT count(1) AS ' refund_order_num' from es_refund  where refund_status = 'APPLY'  " +
                " AND seller_id = ? AND create_time >= ? AND create_time <= ? ";
        return this.daoSupport.queryForInt(sql,UserContext.getSeller().getSellerId(),DateUtil.startOfTodDay(),DateUtil.endOfTodDay()).toString();
    }

    /**
     * 昨天支付订单数
     */
    @Override
    public Integer lastDayPaidOrderNum() {
        String sql = " SELECT COUNT(1) AS 'paid_order_num' FROM es_order " +
                " WHERE pay_status = 'PAY_YES' AND service_status != 'APPLY' AND seller_id = ? " +
                " AND create_time >= ? AND create_time <= ? ";
        return this.daoSupport.queryForInt(sql,UserContext.getSeller().getSellerId(),DateUtil.startOfYesterday(),DateUtil.endOfYesterday());
    }

    /**
     * 昨天 交易额
     */
    @Override
    public Double lastDayTradeAmount() {
        String sql = " SELECT SUM(goods_price) AS 'last_day_trade_amount' FROM es_order WHERE pay_status = 'PAY_YES' " +
                " AND service_status != 'APPLY'  AND seller_id = ? AND create_time >= ? AND create_time <= ? ";
        return this.daoSupport.queryForDouble(sql,UserContext.getSeller().getSellerId(),DateUtil.startOfYesterday(),DateUtil.endOfYesterday());
    }

    /**
     * 昨日客单价
     */
    @Override
    public Double lastDayOrderAvgPrice() {
        String sql = " SELECT COUNT(DISTINCT member_id) AS 'last_day_order_avg_price' FROM es_order " +
                " WHERE pay_status = 'PAY_YES' AND service_status != 'APPLY' " +
                " AND seller_id = ? AND create_time >= ? AND create_time <= ? ";
        // 下单用户
        Integer singleUser = this.daoSupport.queryForInt(sql,UserContext.getSeller().getSellerId(),DateUtil.startOfYesterday(),DateUtil.endOfYesterday());
        // 昨天订单交易额
        Double lastDayTradeAmount = lastDayTradeAmount();
        Double lastDayOrderAvgPrice = 0.0;
        if(singleUser != 0){
            lastDayOrderAvgPrice  = Double.valueOf(String.format("%.1f",CurrencyUtil.div(lastDayTradeAmount, singleUser)));
        }
        return lastDayOrderAvgPrice;
    }

    /**
     * 昨日出单团数
     */
    @Override
    public Integer lastDayActiveDistributorNum() {
        String sql = " SELECT count(DISTINCT d.member_id) AS 'last_day_active_distributor_num' FROM es_distribution d " +
                " LEFT JOIN es_distribution_order dis ON d.member_id = dis.member_id_lv1 " +
                " LEFT JOIN es_order o ON o.order_id = dis.order_id WHERE d.audit_status = 2 AND d.`status` = 1 " +
                " AND o.pay_status = 'PAY_YES' AND o.service_status != 'APPLY' " +
                " AND d.city = ? AND dis.create_time >= ? AND dis.create_time <= ? ";
        String shopCity = getShopInfo().getShopCity();
        return this.daoSupport.queryForInt(sql,shopCity,DateUtil.startOfYesterday(),DateUtil.endOfYesterday());
    }

    /**
     * 昨日新增团长
     */
    @Override
    public Integer lastDayNewDistributorNum() {
        String sql = " SELECT count(1) AS 'last_day_new_distributor_num' FROM es_distribution WHERE audit_status = 2 " +
                " AND `status` = 1  AND city = ? AND audit_time >= ? AND audit_time <= ? ";
        String shopCity = getShopInfo().getShopCity();
        return this.daoSupport.queryForInt(sql,shopCity,DateUtil.startOfYesterday(),DateUtil.endOfYesterday());
    }

    /**
     * 昨日新增自提点
     */
    @Override
    public Integer lastDayNewSiteNum() {
        String sql = " SELECT count(1) AS 'last_day_new_site_num' FROM es_leader WHERE audit_status = 2 AND " +
                " `status` = 1 AND city = ?  AND audit_time >= ? AND audit_time <= ? ";
        String shopCity = getShopInfo().getShopCity();
        return this.daoSupport.queryForInt(sql,shopCity,DateUtil.startOfYesterday(),DateUtil.endOfYesterday());
    }
}
